import pymysql
from todo import Todo

class TodoStorageWithMysql:
    # 静态方法
    @staticmethod
    def get_connection():

        return pymysql.connections.Connection(host='192.168.239.128',database='test',user='root',password='wym0221@YPD',
                                              autocommit=True)

    def init_db(self):
        with self.get_connection() as conn:
            cur = conn.cursor()
            cur.execute('''
            CREATE TABLE IF not EXISTS `todos`(
            `id` int  NOT NULL AUTO_INCREMENT,
            `title` varchar(300) NOT NULL DEFAULT '',
            `completed` int NOT NULL DEFAULT b'0',
            PRIMARY KEY (`id`)
            )ENGINE=INNoDB DEFAULT CHARSET=UTF8MB4 COLLATE=utf8mb4_0900_ai_ci;
            ''')

    def create(self,title:str):
        with self.get_connection() as conn:
            cur = conn.cursor()
            cur.execute("insert into todos(title) values(%s)",(title,))

            cur.execute("select last_insert_id()")
            (id,)=cur.fetchone()
            return Todo(title,id)


    def convert_row_to_object(self,row):
       return Todo(row[1],id=row[0],completed=True if row[2]==1 else False)

    def get_list(self):
        with self.get_connection() as conn:
            cur = conn.cursor()
            cur.execute("select * from todos")
            data = cur.fetchall()
            todo_list=[]
            for row in data:
                todo_list.append(self.convert_row_to_object(row))
            return todo_list
    def get_by_id(self,id):
        with self.get_connection() as conn:
            cur = conn.cursor()
            cur.execute("select * from todos where id = %s",(id,))
            todo = cur.fetchone()
            if todo is not None:
                return self.convert_row_to_object(todo)
            else:
                return None


    def mark_completed(self,id):
        with self.get_connection() as conn:
            cur = conn.cursor()
            cur.execute("update todos set completed=1 where id=(%s)",(id,))
            return self.get_by_id(id)
    #显示已完成






# if __name__ == '__main__':
#     manager = TodoStorage()

